package com.intrasoft.nelsonalva.intrasms;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ParseExcel {

    private String url;
    private int rows;

    public String getUrl(String scid) throws FileNotFoundException, IOException, InvalidFormatException {

        InputStream inputStream = new FileInputStream(new File("urls.xlsx"));
        Workbook wb = WorkbookFactory.create(inputStream);
        Sheet sheet = wb.getSheetAt(0);

        rows = sheet.getLastRowNum();

        for (int i = 1; i <= rows; i++) {
            Row row = sheet.getRow(i);
            Cell cell = (row.getCell(1));
            Cell cell3 = (row.getCell(3));
            cell.setCellType(1);

            String cellstr = cell + "";

            if (cellstr.equals(scid)) {

                url = cell3 + "";
            }
        }
        return url;

    }

    public void getUrl2(String scid, String query) throws FileNotFoundException, IOException, InvalidFormatException, ClassNotFoundException {

        InputStream inputStream = new FileInputStream(new File("urls.xlsx"));
        Workbook wb = WorkbookFactory.create(inputStream);
        Sheet sheet = wb.getSheetAt(0);

        rows = sheet.getLastRowNum();

        int cellposition = 2;

        for (int i = 1; i <= rows; i++) {
            Row row = sheet.getRow(i);
            Cell cell = (row.getCell(cellposition));
            Cell cell2 = (row.getCell(1));
            Cell cell3 = (row.getCell(3));
            cell.setCellType(1);
            cell2.setCellType(1);
            cell3.setCellType(1);

            String cellstr = cell + "";


            url = cell3 + "";

            LANConnector lanconnector = new LANConnector();
            int rows;

            if (cellstr.equals(scid)) {

                try {
                    rows = lanconnector.executeQuery(url, query);

                    String afectadas = "SC " + cell2 + ", filas afectadas: " + rows;

                    System.out.println(afectadas);
                } catch (SQLException ex) {
                    System.out.println("ERROR revisar sintaxis");

                    SCQuery scquery = new SCQuery();
                    try {
                        scquery.bufferStrings();
                    } catch (SQLException ex1) {
                    }
                }

            }

        }

    }

    public void getUrl3(String scid, String query) throws FileNotFoundException, IOException, InvalidFormatException, ClassNotFoundException {


        InputStream inputStream = new FileInputStream(new File("urls.xlsx"));
        Workbook wb = WorkbookFactory.create(inputStream);
        Sheet sheet = wb.getSheetAt(0);

        rows = sheet.getLastRowNum();

        int cellposition = 2;

        for (int i = 1; i <= rows; i++) {
            Row row = sheet.getRow(i);
            Cell cell = (row.getCell(cellposition));
            Cell cell2 = (row.getCell(1));
            Cell cell3 = (row.getCell(3));
            cell.setCellType(1);
            cell2.setCellType(1);
            cell3.setCellType(1);

            int cellint = Integer.parseInt(cell + "");

            url = cell3 + "";

            LANConnector lanconnector = new LANConnector();
            int rows;

            if (cellint != 0 && cellint <= 3) {

                try {
                    rows = lanconnector.executeQuery(url, query);

                    String afectadas = "SC " + cell2 + ", filas afectadas: " + rows;

                    System.out.println(afectadas);
                } catch (SQLException ex) {
                    System.out.println("ERROR revisar sintaxis");

                    SCQuery scquery = new SCQuery();
                    try {
                        scquery.bufferStrings();
                    } catch (SQLException ex1) {
                    }
                }
            }
        }
    }

    public void getUrlSc(String scid, String query) throws FileNotFoundException, IOException, InvalidFormatException, ClassNotFoundException, SQLException {


        InputStream inputStream = new FileInputStream(new File("urls.xlsx"));
        Workbook wb = WorkbookFactory.create(inputStream);
        Sheet sheet = wb.getSheetAt(0);

        rows = sheet.getLastRowNum();

        int cellposition = 2;

        for (int i = 1; i <= rows; i++) {
            Row row = sheet.getRow(i);
            Cell cell = (row.getCell(cellposition));
            Cell cell2 = (row.getCell(1));
            Cell cell3 = (row.getCell(3));
            cell.setCellType(1);
            cell2.setCellType(1);
            cell3.setCellType(1);

            String cellstr = cell + "";
            String sc = cell2 + "";

            url = cell3 + "";

            LANConnector lanconnector = new LANConnector();


            if (cellstr.equals(scid)) {

                try {
                    lanconnector.executeSelect(url, query, sc);

                } catch (SQLException ex) {
                    System.out.println("ERROR revisar sintaxis");

                    SCQuery scquery = new SCQuery();
                    try {
                        scquery.bufferStrings();
                    } catch (SQLException ex1) {
                    }
                }

            }
        }
        SCQuery scquery = new SCQuery();
        scquery.bufferStrings();
    }

    
    public void getUrlSc2(String scid, String query) throws FileNotFoundException, IOException, InvalidFormatException, ClassNotFoundException, SQLException {


        InputStream inputStream = new FileInputStream(new File("urls.xlsx"));
        Workbook wb = WorkbookFactory.create(inputStream);
        Sheet sheet = wb.getSheetAt(0);

        rows = sheet.getLastRowNum();

        int cellposition = 2;

        for (int i = 1; i <= rows; i++) {
            Row row = sheet.getRow(i);
            Cell cell = (row.getCell(cellposition));
            Cell cell2 = (row.getCell(1));
            Cell cell3 = (row.getCell(3));
            cell.setCellType(1);
            cell2.setCellType(1);
            cell3.setCellType(1);

            int cellint = Integer.parseInt(cell + "");

            String sc = cell2 + "";
            url = cell3 + "";

            LANConnector lanconnector = new LANConnector();


            if (cellint != 0 && cellint <= 3) {

                try {
                    lanconnector.executeSelect(url, query, sc);

                } catch (SQLException ex) {
                    System.out.println("ERROR revisar sintaxis");

                    SCQuery scquery = new SCQuery();
                    try {
                        scquery.bufferStrings();
                    } catch (SQLException ex1) {
                    }
                }

            }
        }
        SCQuery scquery = new SCQuery();
        scquery.bufferStrings();
    }
}